﻿<%@ WebHandler Language="C#" Class="ZSDeliveryYearData" %>

using System;
using System.Web;
using Newtonsoft.Json;
using Newtonsoft.Json.Converters;
using System.Collections.Generic;
using System.Linq;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Text;
using System.Web.SessionState;
using System.IO;
using ReportBusiness;
public class ZSDeliveryYearData : IHttpHandler
{
    public bool IsReusable { get { return false; } }
    private uMESReportOracleDAL dal = new uMESReportOracleDAL();
    public void ProcessRequest(HttpContext context)
    {
        //新机月计划执行情况
        if (context.Request.Form["type"] != null && context.Request.Form["type"] == "GetMonthData")
        {
            Dictionary<string, string> para = new Dictionary<string, string>();
            //指标项名称
            if (context.Request.Form["itemname"] != null)
            {
                para["itemname"] = context.Request.Form["itemname"].ToString();
            }

            string sql = @"
SELECT t.planmonth || '月' planmonth,COUNT(t.attributevalue) planqty,
       NVL(SUM(CASE WHEN t.finishdate IS NOT NULL THEN 1 ELSE 0 END),0) finishqty,
       COUNT(t.attributevalue) - NVL(SUM(CASE WHEN t.finishdate IS NOT NULL THEN 1 ELSE 0 END),0) notfinishqty
FROM
(
    SELECT DISTINCT pf.productfamilyname,mfg.mfgordername,u.attributevalue,to_char(mfg.factorycompletiondate,'mm') planmonth,
           ep.finishdate--decode(ep.finishdate,NULL,'未完成','已完成') status
    FROM mfgorder mfg
    LEFT JOIN Ordertype ot ON ot.ordertypeid = mfg.ordertypeid
    LEFT JOIN Userattribute u ON u.parentid = mfg.mfgorderid AND u.userattributename = '发动机'
    LEFT JOIN Userattribute u2 ON u2.parentid = mfg.mfgorderid AND u2.userattributename = '订单类别'
    --查发动机机型
    LEFT JOIN productbase pbm1 ON pbm1.productbaseid = mfg.productbaseid
    LEFT JOIN product pm ON pm.productid = NVL(pbm1.revofrcdid,mfg.productid)
    LEFT JOIN productworkflowmapping pw ON pw.productid = pm.productid AND pw.workflowid = pm.workflowid
    LEFT JOIN Workflowproductfamilylist wff ON wff.workflowid = pw.workflowid
    LEFT JOIN productfamily pf ON pf.productfamilyid = wff.productfamilylistid
    --查询发动机件号
    LEFT JOIN productbase pb ON pb.productbaseid = mfg.productbaseid
    LEFT JOIN product p ON p.productid = NVL(pb.revofrcdid,mfg.productid)
    LEFT JOIN enginepartplan ep ON ep.mfgorderid = mfg.mfgorderid
    LEFT JOIN workflowstep ws ON ws.workflowstepid = ep.workflowstepid
    WHERE p.productid = ep.productid AND ws.sequence < 13 AND ws.workflowstepname = '0120-检验试车'
          AND extract(year from mfg.factorycompletiondate) = extract(year from SYSDATE)
          AND NVL(u2.attributevalue,DECODE(ot.ordertypename,'PD01','批产','PD02','新机','PD03','新机','PD05','大修','PD06','检修')) = '新机'
) t
GROUP BY t.planmonth
ORDER BY t.planmonth
";

            DataTable dt = dal.GetDataTable(sql);
            var json = JsonConvert.SerializeObject(dt);
            context.Response.ContentType = "text/json";
            context.Response.Write(json.ToString());
            context.Response.End();
        }

        //各机型完成情况
        if (context.Request.Form["type"] != null && context.Request.Form["type"] == "GetProductfamilyData")
        {
            Dictionary<string, string> para = new Dictionary<string, string>();

            string sql = @"
SELECT t.productfamilyname,COUNT(t.attributevalue) planqty,
       NVL(SUM(CASE WHEN t.finishdate IS NOT NULL THEN 1 ELSE 0 END),0) finishqty,
       COUNT(t.attributevalue) - NVL(SUM(CASE WHEN t.finishdate IS NOT NULL THEN 1 ELSE 0 END),0) notfinishqty
FROM
(
    SELECT DISTINCT pf.productfamilyname,mfg.mfgordername,u.attributevalue,
           ep.finishdate--decode(ep.finishdate,NULL,'未完成','已完成') status
    FROM mfgorder mfg
    LEFT JOIN Ordertype ot ON ot.ordertypeid = mfg.ordertypeid
    LEFT JOIN Userattribute u ON u.parentid = mfg.mfgorderid AND u.userattributename = '发动机'
    LEFT JOIN Userattribute u2 ON u2.parentid = mfg.mfgorderid AND u2.userattributename = '订单类别'
    --查发动机机型
    LEFT JOIN productbase pbm1 ON pbm1.productbaseid = mfg.productbaseid
    LEFT JOIN product pm ON pm.productid = NVL(pbm1.revofrcdid,mfg.productid)
    LEFT JOIN productworkflowmapping pw ON pw.productid = pm.productid AND pw.workflowid = pm.workflowid
    LEFT JOIN Workflowproductfamilylist wff ON wff.workflowid = pw.workflowid
    LEFT JOIN productfamily pf ON pf.productfamilyid = wff.productfamilylistid
    --查询发动机件号
    LEFT JOIN productbase pb ON pb.productbaseid = mfg.productbaseid
    LEFT JOIN product p ON p.productid = NVL(pb.revofrcdid,mfg.productid)
    LEFT JOIN enginepartplan ep ON ep.mfgorderid = mfg.mfgorderid
    LEFT JOIN workflowstep ws ON ws.workflowstepid = ep.workflowstepid
    WHERE p.productid = ep.productid AND ws.sequence < 13 AND ws.workflowstepname = '0120-检验试车'
          AND extract(year from mfg.factorycompletiondate) = extract(year from SYSDATE)
          AND NVL(u2.attributevalue,DECODE(ot.ordertypename,'PD01','批产','PD02','新机','PD03','新机','PD05','大修','PD06','检修')) = '新机'
) t
GROUP BY t.productfamilyname
ORDER BY t.productfamilyname
";
            DataTable dt = dal.GetDataTable(sql);
            var json = JsonConvert.SerializeObject(dt);
            context.Response.ContentType = "text/json";
            context.Response.Write(json.ToString());
            context.Response.End();
        }
    }
}